﻿using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace Vbyte.SharpOrm
{
    /// <summary>
    /// 数据库架构信息辅助
    /// </summary>
    public static class DbSchemaHelper
    {
        /*
         -- select @@Version
         Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 
         Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) 
         
         -- sp_databases
         DATABASE_NAME	DATABASE_SIZE	REMARKS
         master	5376	NULL
         
         */

        /// <summary>
        /// 获取SQL2005/2008数据库表的数据表定义
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="connectionStr">数据库连接字符串</param>
        /// <returns></returns>
        /// <remarks>字段名:表名 字段序号 字段名 标识 主键 类型 占用字节数 长度 小数位数 允许空 默认值 字段说明</remarks>
        public static DataTable GetSql2005TableDoc(string tableName, string connectionStr)
        {
            string tableFilter = string.IsNullOrEmpty(tableName) ? "" : "where d.name = '" + tableName + "'";
            string sql = @"SELECT (case when a.colorder=1 then d.name else '' end) 表名, 
					        a.colorder 字段序号, a.name 字段名,
					         (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
					         (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id)
						           AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id)
						           AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id)
						           AND (name = a.name)))))))
				           AND (xtype = 'PK'))>0 then '√' else '' end) 主键, 
			         b.name 类型, a.length 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
			         isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
			         (case when a.isnullable=1 then '√'else '' end) 允许空,
			         isnull(e.text,'') 默认值, isnull(g.[value],'') AS 字段说明
			          FROM syscolumns a left join systypes b on a.xtype=b.xusertype
			          inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
			        left join syscomments e on a.cdefault=e.id
			        left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id " 
                    + tableFilter + " order by a.id,a.colorder";

            return GetSqlTableDoc(sql, connectionStr);

        }

        private static DataTable GetSqlTableDoc(string sqlQuery, string connectionStr)
        {
            DataTable dTab = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                conn.Open();
                using (SqlDataAdapter adp = new SqlDataAdapter(sqlQuery, conn))
                {
                    adp.Fill(dTab);
                }
                conn.Close();
            }
            return dTab;
        }

        /// <summary>
        /// 获取SQL2000数据库表的数据表定义
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="connectionStr">数据库连接字符串</param>
        /// <returns></returns>
        public static DataTable GetSql2000TableDoc(string tableName, string connectionStr)
        {
            string tableFilter = string.IsNullOrEmpty(tableName) ? "" : "where d.name = '" + tableName + "'";
            string sql = @"SELECT  表名=case when a.colorder=1 then d.name else '' end, 
                        字段序号=a.colorder, 字段名=a.name, 
                        标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 
                        主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name 
                           in (SELECT name FROM sysindexes WHERE indid 
                            in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid  ))) 
                             then '√' else '' end,
                         类型=b.name, 
                         占用字节数=a.length, 
                         长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 
                         小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 
                         允许空=case when a.isnullable=1 then '√'else '' end, 
                         默认值=isnull(e.text,''), 
                         字段说明=isnull(g.[value],'')
                         FROM syscolumns a left join systypes b on a.xtype=b.xusertype 
                         inner join sysobjects d on a.id=d.id and d.xtype='U' and  d.name<>'dtproperties' 
                         left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid "
                        + tableFilter + " order by a.id,a.colorder";

            return GetSqlTableDoc(sql, connectionStr);
        }

        /// <summary>
        /// 获取OleDB Provider的数据库表的数据表定义
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="connectionStr">数据库连接字符串</param>
        /// <returns></returns>
        public static DataTable GetOleDbTableDoc(string tableName, string connectionStr)
        {
            DataTable dTab = new DataTable();
            using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionStr))
            {
                conn.Open();
                //System.Data.OleDb.OleDbDataAdapter adp = new System.Data.OleDb.OleDbDataAdapter("", conn);
                conn.Close();
            }
            return dTab;
        }
    }
}
